package com.boss.salary.dao.impl;


import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IPersonOperateDao;
import com.boss.salary.entity.AgencyVerify;
import com.boss.salary.entity.PersonOperate;
import com.boss.salary.vo.UnitSalaryReportVO;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class PersonOperateDaoImpl extends BaseDaoImpl implements IPersonOperateDao {

    @Override
    public List<PersonOperate> queryPersonOperatePage(PaginationDTO paginationDTO) {
        StringBuilder sql = new StringBuilder();

        sql.append("select id, create_time, update_time, person_id, person_name, sex_code, iden_type_code,")
                .append("operate_code, iden_no, nation, verify_state, delete_type,")
                .append("nat_code, birthday, state, work_init_date, iden_is_verify, fundmode, out_to_agency,")
                .append("person_address, agency_id, dept_id, agency_code, enter_agency_date,")
                .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state, ")
                .append("change_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_deleted, ")
                .append("salary_month, person_type_id, post_id, grade_id, level_id,phone,func_id,func_name,func_code ");
        sql.append(" from ").append(TableName.PERSON_OPERATE);
        sql.append(" order by create_time");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(null, paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(PersonOperate.class), params);
    }

    @Override
    public int queryPersonOperateTotal() {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) from ").append(TableName.PERSON_OPERATE);
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class);
    }

    @Override
    public void savePersonOperate(PersonOperate personOperate) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.PERSON_OPERATE);
        sql.append("(id, create_time, update_time, person_id, person_name, sex_code, iden_type_code,")
                .append("operate_code, iden_no, nation, verify_state, delete_type,")
                .append("nat_code, birthday, state, work_init_date, iden_is_verify, fundmode, out_to_agency, in_from_agency, ")
                .append("person_address, agency_id, dept_id, agency_code, enter_agency_date,")
                .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state, ")
                .append("change_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_deleted ,iden_verify_state,is_real,salary_month, mof_div_code, ")
                .append(" person_type_id, post_id, grade_id, level_id,personnel_code,phone,NATURE_OF_FUNDS_CODE,FUNCTIONAL_SUBJECTS_CODE,DEPT_ECONOMIC_SUBJECTS_CODE,GOV_DEPT_CLASS_CODE,func_id,func_name,func_code )");
        sql.append("values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,")
                .append("?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        Object[] params = new Object[]{
                personOperate.getId(), personOperate.getCreateTime(), personOperate.getUpdateTime(),
                personOperate.getPersonId(), personOperate.getPersonName().trim(), personOperate.getSexCode(),
                personOperate.getIdenTypeCode(), personOperate.getOperateCode(), personOperate.getIdenNo().trim(),
                personOperate.getNation(), personOperate.getVerifyState(), personOperate.getDeleteType(),
                personOperate.getNatCode(), personOperate.getBirthday(), personOperate.getState(),
                personOperate.getWorkInitDate(), personOperate.getIdenIsVerify(), personOperate.getFundmode(),
                personOperate.getOutToAgency(), personOperate.getInFromAgency(), personOperate.getPersonAddress(), personOperate.getAgencyId(),
                personOperate.getDeptId(), personOperate.getAgencyCode(), personOperate.getEnterAgencyDate(),
                personOperate.getRetireDate(), personOperate.getPersonTypeCode(), personOperate.getPersonIdeCode(),
                personOperate.getEndDate(), personOperate.getDeadDate(), personOperate.getRemarks(),
                personOperate.getSalaCardNo(), personOperate.getSalaCardBank(), personOperate.getGovCredCardNo(),
                personOperate.getGovCredCardBank(), personOperate.getStartDate(), personOperate.getMeritPayCode(),
                personOperate.getSchRecCode(), personOperate.getPosCode(), personOperate.getGrCode(),
                personOperate.getTecGrCode(), personOperate.getIsAuth(), personOperate.getSalaGrCode(),
                personOperate.getPersonState(), personOperate.getChangeState(), personOperate.getPerSourceCode(),
                personOperate.getIsSpecialPost(), personOperate.getIsInsurance(), personOperate.getIsUniSala(),
                personOperate.getIsDeleted(),personOperate.getIdenVerifyState(),personOperate.getIsReal(),
                personOperate.getSalaryMonth(),personOperate.getMofDivCode(), personOperate.getPersonTypeId(),
                personOperate.getPostId(), personOperate.getGradeId(), personOperate.getLevelId(),
                personOperate.getPersonnelCode(),personOperate.getPhone(),personOperate.getNatureOfFundsCode(),
                personOperate.getFunctionalSubjectsCode(),personOperate.getDeptEconomicSubjectsCode(),personOperate.getGovDeptClassCode(),
                personOperate.getFuncId(),personOperate.getFuncName(),personOperate.getFuncCode()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public int verifyIdenNoAndBankCardRepeatFromInfo(String idenNo, String salaCardNo, String personId,String mofDivCode) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" select count(*) from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 and pet.mof_div_code = ? ");
        params.add(mofDivCode);
                sb.append(" where ").append(" pio.is_deleted = 0 and pio.mof_div_code = ? ");
        params.add(mofDivCode);
                sb.append(" and ( pio.iden_no = ? or pet.sala_card_no = ? ) ");
        params.add(idenNo);
        params.add(salaCardNo);
        if(!StringUtils.isEmpty(personId)){
            sb.append(" and pio.id != ? ");
            params.add(personId);
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), int.class, params.toArray());
    }

    @Override
    public int verifyIdenNoAndBankCardRepeatFromOperate(String idenNo, String salaCardNo, String personId,String mofDivCode){
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" select count(*) from ")
                .append(TableName.PERSON_OPERATE)
                .append(" where ").append(" is_deleted = 0 ")
                .append(" and mof_div_code = ? ");
        params.add( mofDivCode );
                sb.append(" and verify_state != '2' and is_real != 1 ")
                .append(" and ( iden_no = ? or sala_card_no = ? ) ");
        params.add(idenNo);
        params.add(salaCardNo);
        if(!StringUtils.isEmpty(personId)){
            sb.append(" and person_id != ? ");
            params.add(personId);
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), int.class, params.toArray());
    }

    @Override
    public UnitSalaryReportVO selectPersonPlusAndMinusCount(String agencyId, String salaryMonth) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" nvl(sum(case when operate_code = 'add' or operate_code = 'in' then 1 else 0 end), 0) unitPlusTotal, ")
                .append(" nvl(sum(case when operate_code = 'delete' or operate_code = 'out' then 1 else 0 end), 0) unitMinusTotal ")
                .append(" from ").append(TableName.PERSON_OPERATE)
                .append(" where ").append(" is_deleted = 0 and verify_state = '2' and is_real = 1 and agency_id = ? and salary_month = ? ");
        return super.getjdbcTemplate().queryForObject(sb.toString(), new BeanPropertyRowMapper<>(UnitSalaryReportVO.class), agencyId, salaryMonth);
    }

    @Override
    public void updatePersonOperate(PersonOperate personOperate) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.PERSON_OPERATE);
        sql.append(" set create_time=?, update_time=?, person_id=?, person_name=?, sex_code=?, iden_type_code=?,")
                .append("operate_code=?, iden_no=?, nation=?, verify_state=?, delete_type=?,")
                .append("nat_code=?, birthday=?, state=?, work_init_date=?, iden_is_verify=?, fundmode=?, out_to_agency=?,")
                .append("person_address=?, agency_id=?, dept_id=?, agency_code=?, enter_agency_date=?,")
                .append("retire_date=?, person_type_code=?, person_ide_code=?, end_date=?, dead_date=?, remarks=?, sala_card_no=?,")
                .append("sala_card_bank=?, gov_cred_card_no=?, gov_cred_card_bank=?, start_date=?, merit_pay_code=?,")
                .append("sch_rec_code=?, pos_code=?, gr_code=?, tec_gr_code=?, is_auth=?, sala_gr_code=?, person_state=?, ")
                .append("change_state=?, per_source_code=?, is_special_post=?, is_insurance=?, is_uni_sala=?, is_deleted=?,iden_verify_state=?, is_real = ?, salary_month = ?, ")
                .append(" func_id = ?,func_name = ?,func_code = ? ");
        sql.append(" where id=?");
        Object[] params = new Object[]{
                personOperate.getCreateTime(), personOperate.getUpdateTime(),
                personOperate.getPersonId(), personOperate.getPersonName(), personOperate.getSexCode(),
                personOperate.getIdenTypeCode(), personOperate.getOperateCode(), personOperate.getIdenNo(),
                personOperate.getNation(), personOperate.getVerifyState(), personOperate.getDeleteType(),
                personOperate.getNatCode(), personOperate.getBirthday(), personOperate.getState(),
                personOperate.getWorkInitDate(), personOperate.getIdenIsVerify(), personOperate.getFundmode(),
                personOperate.getOutToAgency(), personOperate.getPersonAddress(), personOperate.getAgencyId(),
                personOperate.getDeptId(), personOperate.getAgencyCode(), personOperate.getEnterAgencyDate(),
                personOperate.getRetireDate(), personOperate.getPersonTypeCode(), personOperate.getPersonIdeCode(),
                personOperate.getEndDate(), personOperate.getDeadDate(), personOperate.getRemarks(),
                personOperate.getSalaCardNo(), personOperate.getSalaCardBank(), personOperate.getGovCredCardNo(),
                personOperate.getGovCredCardBank(), personOperate.getStartDate(), personOperate.getMeritPayCode(),
                personOperate.getSchRecCode(), personOperate.getPosCode(), personOperate.getGrCode(),
                personOperate.getTecGrCode(), personOperate.getIsAuth(), personOperate.getSalaGrCode(),
                personOperate.getPersonState(), personOperate.getChangeState(), personOperate.getPerSourceCode(),
                personOperate.getIsSpecialPost(), personOperate.getIsInsurance(), personOperate.getIsUniSala(),
                personOperate.getIsDeleted(),personOperate.getIdenVerifyState(), personOperate.getIsReal(), personOperate.getSalaryMonth(),
                personOperate.getFuncId(),personOperate.getFuncName(),personOperate.getFuncCode(),
                personOperate.getId()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void deletePersonOperate(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.PERSON_OPERATE);
        sql.append(" set is_deleted='1' ")
                .append("where id=?");
        Object[] params = new Object[]{
                id
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public List<PersonOperate> getPersonOperatePage(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select a.id,a.create_time, a.update_time,a.person_id, a.person_name,a.sex_code,a.iden_type_code,a.operate_code,a.iden_no,a.nation,a.nat_code, ")
                .append("a.birthday,a.state,a.work_init_date,a.iden_is_verify,a.fundmode,a.out_to_agency,a.delete_type,a.person_address,a.agency_id,")
                .append("a.dept_id,a.agency_code,a.enter_agency_date,a.retire_date,a.person_type_code,a.person_ide_code,a.end_date,a.dead_date,a.remarks,")
                .append("a.sala_card_no,a.sala_card_bank,a.gov_cred_card_no,a.gov_cred_card_bank,a.start_date,a.merit_pay_code,a.sch_rec_code,a.pos_code,a.gr_code,")
                .append("a.tec_gr_code,a.is_auth,a.sala_gr_code,a.person_state,a.change_state,a.verify_state,a.per_source_code,a.is_special_post,a.is_insurance,a.is_uni_sala,a.is_deleted,a.iden_verify_state,a.is_real,a.phone,a.func_id,a.func_name,a.func_code")
                .append(" from ").append(TableName.PERSON_INFO).append("  b join ");
        sql.append("(select id,create_time,update_time,person_id,person_name,sex_code,iden_type_code,operate_code,iden_no,nation,nat_code, ")
                .append("birthday,state,work_init_date,iden_is_verify,fundmode,out_to_agency,delete_type,person_address,agency_id,")
                .append("dept_id,agency_code,enter_agency_date,retire_date,person_type_code,person_ide_code,end_date,dead_date,remarks,")
                .append("sala_card_no,sala_card_bank,gov_cred_card_no,gov_cred_card_bank,start_date,merit_pay_code,sch_rec_code,pos_code,gr_code,")
                .append("tec_gr_code,is_auth,sala_gr_code,person_state,change_state,verify_state,per_source_code,is_special_post,is_insurance,is_uni_sala,is_deleted,iden_verify_state,is_real,phone,func_id,func_name,func_code ")
                .append(" from ").append(TableName.PERSON_OPERATE)
                .append(" where  update_time in ( select max(update_time) from  ").append(TableName.PERSON_OPERATE)
                .append(" where is_deleted= ? and verify_state=? and is_real=? and (operate_code in(?,?) or operate_code = ?) and agency_id=?  ");
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(SystemDictConstant.IS_AUDIT);
        paramList.add(SystemDictConstant.ENABLE);
        paramList.add(SystemDictConstant.ADD_PERSON);
        paramList.add(SystemDictConstant.IN_PERSON);
        paramList.add(SystemDictConstant.CHECK_IDEN_NO);
        paramList.add(queryParam.get("agencyId"));
        sql.append(" group by person_id ) ");

        if (!StringUtils.isEmpty(queryParam)) {
            String flag = queryParam.get("flag").toString();
            if ("auditAll".equals(flag)) {
                sql.append(" and iden_verify_state in (?, ?)");
                paramList.add(SystemDictConstant.IS_SUBMIT);
                paramList.add(SystemDictConstant.IS_AUDIT);
            } else if("all".equals(flag)){
                sql.append(" and iden_verify_state in (?, ?, ?)");
                paramList.add(SystemDictConstant.NO_SUBMIT);
                paramList.add(SystemDictConstant.IS_SUBMIT);
                paramList.add(SystemDictConstant.IS_REJECT);
            }else if (!"all".equals(flag)) {
                sql.append(" and iden_verify_state = ?");
                paramList.add(flag);
            }
            if (!StringUtils.isEmpty(queryParam.get("idLength"))) {
                sql.append(" and length(iden_no)= ?");
                paramList.add(queryParam.get("idLength"));
            }
            if (!StringUtils.isEmpty(queryParam.get("gzlb"))) {
                sql.append(" and merit_pay_code = ?");
                paramList.add(queryParam.get("gzlb"));
            }
            if(!StringUtils.isEmpty(queryParam.get("agencyId"))){
                sql.append(" and agency_id = ?");
                paramList.add(queryParam.get("agencyId"));
            }
            if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
                sql.append(" and mof_div_code = ?");
                paramList.add(queryParam.get("mofDivCode"));
            }
        }

        sql.append(") a on a.person_id=b.id where b.is_deleted=?  order by  a.update_time desc");
        paramList.add(SystemDictConstant.IS_FALSE);

        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(PersonOperate.class), params);
    }

    @Override
    public int getPersonOperateTotal(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) from ").append(TableName.PERSON_INFO).append("  b join ");
        sql.append("(select person_id from  ").append(TableName.PERSON_OPERATE);
        sql.append("  where update_time in ( select max(update_time) from  ").append(TableName.PERSON_OPERATE)
                .append(" where is_deleted= ? and verify_state=? and is_real=? and (operate_code in(?,?) or operate_code = ?) and agency_id=? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(SystemDictConstant.IS_AUDIT);
        paramList.add(SystemDictConstant.ENABLE);
        paramList.add(SystemDictConstant.ADD_PERSON);
        paramList.add(SystemDictConstant.IN_PERSON);
        paramList.add(SystemDictConstant.CHECK_IDEN_NO);
        paramList.add(queryParam.get("agencyId"));
        sql.append(" group by person_id ) ");

        if (!StringUtils.isEmpty(queryParam)) {
            String flag = queryParam.get("flag").toString();
            if ("auditAll".equals(flag)) {
                sql.append(" and iden_verify_state in (?, ?)");
                paramList.add(SystemDictConstant.IS_SUBMIT);
                paramList.add(SystemDictConstant.IS_AUDIT);
            } else if("all".equals(flag)){
                sql.append(" and iden_verify_state in (?, ?, ?)");
                paramList.add(SystemDictConstant.NO_SUBMIT);
                paramList.add(SystemDictConstant.IS_SUBMIT);
                paramList.add(SystemDictConstant.IS_REJECT);
            }else if (!"all".equals(flag)) {
                sql.append(" and iden_verify_state = ?");
                paramList.add(flag);
            }
            if (!StringUtils.isEmpty(queryParam.get("idLength"))) {
                sql.append(" and length(iden_no)= ?");
                paramList.add(queryParam.get("idLength"));
            }
            if (!StringUtils.isEmpty(queryParam.get("gzlb"))) {
                sql.append(" and merit_pay_code = ?");
                paramList.add(queryParam.get("gzlb"));
            }
            if(!StringUtils.isEmpty(queryParam.get("agencyId"))){
                sql.append(" and agency_id = ?");
                paramList.add(queryParam.get("agencyId"));
            }
            if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
                sql.append(" and mof_div_code = ?");
                paramList.add(queryParam.get("mofDivCode"));
            }
            sql.append(" ) a  on a.person_id=b.id  where b.is_deleted=?");
            paramList.add(SystemDictConstant.IS_FALSE);
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramList.toArray());
    }

    @Override
    public List<PersonOperate> getPersonChangeListPage(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" distinct spo.person_id,spo.person_name,spo.state, spo.iden_no, spo.operate_code, spo.out_to_agency, spo.in_from_agency ");
        sql.append(" FROM ");
        sql.append(TableName.PERSON_OPERATE).append(" spo ")
                .append(" JOIN ").append(TableName.AGENCY_VERIFY).append(" sav ")
                .append(" ON spo.agency_id = sav.agency_id ");
        sql.append(" where ")
                .append(" sav.is_month_end = ?")
                .append(" and sav.is_auto = 0 ")
                .append(" and spo.salary_month=sav.salary_month ")
                .append(" and spo.agency_id = ? ")
                .append(" and operate_code != ? ")
                .append(" and spo.verify_state = ? ")
                .append(" and spo.is_real = ? ");
        sql.append(" order by spo.person_id ");
        Object[] objects = new Object[]{
                SystemDictConstant.IS_FALSE, queryParam.get("agencyId"), SystemDictConstant.CHECK_IDEN_NO,
                SystemDictConstant.IS_AUDIT, SystemDictConstant.IS_TRUE
        };
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(objects, paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(PersonOperate.class), params);
    }

    @Override
    public int getPersonChangeListTotal(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
//        sql.append(" count(distinct spo.person_id, spo.operate_code) ");
        sql.append(" count(distinct spo.person_id) ");
        sql.append(" FROM ");
        sql.append(TableName.PERSON_OPERATE).append(" spo ")
                .append(" JOIN ").append(TableName.AGENCY_VERIFY).append(" sav ")
                .append(" ON spo.agency_id = sav.agency_id ");
        sql.append(" where ")
                .append(" sav.is_month_end = ?")
                .append(" and spo.salary_month = sav.salary_month")
                .append(" and spo.agency_id = ?")
                .append(" and spo.operate_code != ? ")
                .append(" and spo.verify_state = ? ")
                .append(" and spo.is_real = ?");
        sql.append(" order by spo.update_time ");
        Object[] objects = new Object[]{
                SystemDictConstant.IS_FALSE, queryParam.get("agencyId"), SystemDictConstant.CHECK_IDEN_NO,
                SystemDictConstant.IS_AUDIT, SystemDictConstant.IS_TRUE
        };
        return this.getjdbcTemplate().queryForObject(sql.toString(), Integer.class, objects);
    }

    @Override
    public int getPersonChangeTotal(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" count(distinct person_id) ");
        sql.append(" FROM ");
        sql.append(TableName.PERSON_OPERATE).append(" spo ")
                .append(" JOIN ").append(TableName.AGENCY_VERIFY).append(" sav ")
                .append(" ON spo.agency_id = sav.agency_id ")
                .append(" JOIN ").append(TableName.PERSON_INFO).append(" spi ")
                .append(" ON spo.person_id = spi.id ");
        sql.append(" where ")
                .append(" sav.is_month_end = ?")
                .append(" and spo.salary_month =sav.salary_month   ")
                .append(" and spo.agency_id = ?")
                .append(" and operate_code != ? ")
                .append(" and verify_state = ? ")
                .append(" and is_real = ?");
        sql.append(" order by spo.update_time ");
        Object[] objects = new Object[]{
                SystemDictConstant.IS_FALSE, queryParam.get("agencyId"), SystemDictConstant.CHECK_IDEN_NO,
                SystemDictConstant.IS_AUDIT, SystemDictConstant.IS_TRUE
        };
        return this.getjdbcTemplate().queryForObject(sql.toString(), Integer.class, objects);
    }

    @Override
    public List<PersonOperate> getPersonInfoChangeOperatePage(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select  (case when  fio.middle_id is not null and fio.middle_id != '' then 1 else 0 end ) existsFile ,po.id, po.create_time, po.update_time, po.person_id, po.person_name,po.sex_code,po.iden_type_code, ")
                .append(" po.operate_code, po.iden_no, po.nation, po.nat_code,")
                .append("po.birthday,po.state,po.work_init_date,po.iden_is_verify,po.fundmode,po.out_to_agency,po.in_from_agency,po.delete_type,po.person_address,po.agency_id,")
                .append("po.dept_id,po.agency_code,po.enter_agency_date,po.retire_date,po.person_type_code,po.person_ide_code,po.end_date,po.dead_date,po.remarks,")
                .append("po.sala_card_no,po.sala_card_bank,po.gov_cred_card_no,po.gov_cred_card_bank,po.start_date,po.merit_pay_code,po.sch_rec_code,po.pos_code,po.gr_code,")
                .append("po.tec_gr_code, po.is_auth, po.sala_gr_code,po.person_state,po.change_state,po.verify_state,po.per_source_code,po.is_special_post,po.is_insurance, ")
                .append("po.is_uni_sala, po.is_deleted, po.iden_verify_state, po.is_real, po.salary_month, po.person_type_id, ")
                .append(" po.post_id, po.grade_id, po.level_id ,po.personnel_code,po.phone,po.NATURE_OF_FUNDS_CODE,po.FUNCTIONAL_SUBJECTS_CODE,")
                .append("po.DEPT_ECONOMIC_SUBJECTS_CODE,po.GOV_DEPT_CLASS_CODE,po.func_id,po.func_name,po.func_code")
                .append(" from ").append(TableName.PERSON_OPERATE).append(" po ")
                .append(" join ").append(" ( select max(update_time) update_time,person_id from  ").append(TableName.PERSON_OPERATE)
                .append(" where is_deleted = ?");
        paramList.add(SystemDictConstant.IS_FALSE);
        sql.append(" and operate_code != ? ");
        paramList.add(SystemDictConstant.CHECK_IDEN_NO);
//        if (SystemDictConstant.IS_AUDIT.equals(queryParam.get("flag").toString())){
            sql.append(" and agency_id = ?");
            paramList.add(queryParam.get("agencyId"));
//        }
        sql.append(" group by person_id ) maxtime on po.update_time=maxtime.update_time AND po.person_id=maxtime.person_id ")
                .append(" left join ").append(" ( select distinct middle_id from ").append(TableName.SAL_FILE_INFO)
                .append(" where ").append(" is_deleted = 0 and agency_id = ? ")
                .append(" and middle_table_code = '2' and salary_month = ? ) fio ")
                .append(" on ").append(" fio.middle_id = po.person_id ");
        paramList.add(queryParam.get("agencyId"));
        paramList.add(queryParam.get("salaryMonth"));
//                .append(" LEFT JOIN ").append(TableName.PERSON_INFO).append(" pi ")
//                .append(" ON po.person_id = pi.id ")
        sql.append(" where 1=1 ");
//                .append("  po.update_time in ( select max(update_time) from  ").append(TableName.PERSON_OPERATE).append(" where is_deleted = ?");
//        paramList.add(SystemDictConstant.IS_FALSE);
//        sql.append(" and operate_code != ? ");
//        paramList.add(SystemDictConstant.CHECK_IDEN_NO);
//        //sql.append(" and agency_id = ?");
//       //paramList.add(queryParam.get("agencyId"));
//                        /*.append(" or (operate_code = '")
//                        .append(SystemDictConstant.CHECK_IDEN_NO)
//                        .append("' and verify_state = '")
//                        .append(SystemDictConstant.IS_AUDIT).append("')");*/
//        sql.append("group by person_id ) ");
        if(!StringUtils.isEmpty(queryParam)){
            String flag = queryParam.get("flag").toString();
            if ("auditAll".equals(flag)) {
                sql.append(" and verify_state in (?,?)");
                paramList.add(SystemDictConstant.IS_SUBMIT);
                paramList.add(SystemDictConstant.IS_AUDIT);
            } else if (!"all".equals(flag)) {
                sql.append(" and verify_state = ?");
                paramList.add(flag);
                if(SystemDictConstant.IS_AUDIT.equals(flag)){
                    sql.append(" and salary_month = ? ");
                    paramList.add(queryParam.get("salaryMonth"));
                }
            }
        }
        // 不等于已审核页签
        if (!SystemDictConstant.IS_AUDIT.equals(queryParam.get("flag").toString())){
            sql.append(" AND (case when ((operate_code = ? or operate_code = ?)  and verify_state = ?  and is_real = ? ) then  1 else po.is_deleted end) = ?");
            paramList.add(SystemDictConstant.DELETE_PERSON);
            paramList.add(SystemDictConstant.OUT_PERSON);
            paramList.add(SystemDictConstant.IS_AUDIT);
            paramList.add(SystemDictConstant.IS_TRUE);
            paramList.add(SystemDictConstant.IS_FALSE);
        }
        sql.append(" and agency_id = ?");
        paramList.add(queryParam.get("agencyId"));
        if(!StringUtils.isEmpty(queryParam.get("meritPayCode"))) {
            sql.append(" and merit_pay_code = ? ");
            paramList.add(queryParam.get("meritPayCode"));
        }
        if(!StringUtils.isEmpty(queryParam.get("posCode"))) {
            sql.append(" and pos_code = ?");
            paramList.add(queryParam.get("posCode"));
        }
        if(!StringUtils.isEmpty(queryParam.get("grCode"))) {
            sql.append(" and gr_code = ?");
            paramList.add(queryParam.get("grCode"));
        }
        if(!StringUtils.isEmpty(queryParam.get("personName"))) {
            sql.append(" and po.person_name like ?");
            paramList.add('%'+queryParam.get("personName").toString()+'%');
           // paramList.add(queryParam.get("personName"));
        }
        if(!StringUtils.isEmpty(queryParam.get("idenNo"))) {
            sql.append(" and po.iden_no like ?");
            paramList.add('%'+queryParam.get("idenNo").toString()+'%');
            // paramList.add(queryParam.get("idenNo"));
        }
        if(!StringUtils.isEmpty(queryParam.get("state"))){
            sql.append(" and po.state=?");
            paramList.add(queryParam.get("state"));
        }
        if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
            sql.append(" and po.mof_div_code=?");
            paramList.add(queryParam.get("mofDivCode"));
        }
        if(!StringUtils.isEmpty(queryParam.get("deptid"))) {
            sql.append(" and po.dept_id = ?");
            paramList.add(queryParam.get("deptid"));
        }
        if(!StringUtils.isEmpty(queryParam.get("personnelCode"))) {
            sql.append(" and po.personnel_code like ?");
            paramList.add('%'+queryParam.get("personnelCode").toString()+'%');
           // paramList.add(queryParam.get("personnelCode"));
        }
        sql.append(" order by ")
            .append(" CASE ")
            .append(" WHEN operate_code = 'in'  and verify_state is null  THEN ")
            .append(" 5 ")
            .append(" ELSE ")
            .append(" 0 ")
            .append(" END  , case when po.salary_month=? then 1 else 0 end desc, ")
            .append(" po.dept_id,-po.show_order desc ");
        paramList.add(queryParam.get(" salaryMonth"));
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper<>(PersonOperate.class), params);
    }

    @Override
    public int getPersonInfoChangeOperateTotal(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) from (select po.id ")
                .append(" from ").append(TableName.PERSON_OPERATE).append(" po ")
                .append(" join ").append(" ( select max(update_time) update_time,person_id from  ").append(TableName.PERSON_OPERATE)
                .append(" where is_deleted = ?");
        paramList.add(SystemDictConstant.IS_FALSE);
        sql.append(" and operate_code != ? ");
        paramList.add(SystemDictConstant.CHECK_IDEN_NO);
//        if (SystemDictConstant.IS_AUDIT.equals(queryParam.get("flag").toString())){
            sql.append(" and agency_id = ?");
            paramList.add(queryParam.get("agencyId"));
//        }
        sql.append("group by person_id ) maxtime on po.update_time=maxtime.update_time AND po.person_id=maxtime.person_id")
                .append(" where 1=1 ");

        if(!StringUtils.isEmpty(queryParam)){
            String flag = queryParam.get("flag").toString();
            if ("auditAll".equals(flag)) {
                sql.append(" and verify_state in (?,?)");
                paramList.add(SystemDictConstant.IS_SUBMIT);
                paramList.add(SystemDictConstant.IS_AUDIT);
            } else if (!"all".equals(flag)) {
                sql.append(" and verify_state = ?");
                paramList.add(flag);
                if(SystemDictConstant.IS_AUDIT.equals(flag)){
                    sql.append(" and salary_month = ? ");
                    paramList.add(queryParam.get("salaryMonth"));
                }
            }
        }
        // 不等于已审核页签
        if (!SystemDictConstant.IS_AUDIT.equals(queryParam.get("flag").toString())){
            sql.append(" AND (case when ((operate_code = ? or operate_code = ?)  and verify_state = ?  and is_real = ? ) then 1 else po.is_deleted end) = ?");
            paramList.add(SystemDictConstant.DELETE_PERSON);
            paramList.add(SystemDictConstant.OUT_PERSON);
            paramList.add(SystemDictConstant.IS_AUDIT);
            paramList.add(SystemDictConstant.IS_TRUE);
            paramList.add(SystemDictConstant.IS_FALSE);
        }

        sql.append(" and agency_id = ?");
        paramList.add(queryParam.get("agencyId"));
        if(!StringUtils.isEmpty(queryParam.get("meritPayCode"))) {
            sql.append(" and merit_pay_code = ? ");
            paramList.add(queryParam.get("meritPayCode"));
        }
        if(!StringUtils.isEmpty(queryParam.get("posCode"))) {
            sql.append(" and pos_code = ?");
            paramList.add(queryParam.get("posCode"));
        }
        if(!StringUtils.isEmpty(queryParam.get("grCode"))) {
            sql.append(" and gr_code = ?");
            paramList.add(queryParam.get("grCode"));
        }
        if(!StringUtils.isEmpty(queryParam.get("personName"))) {
            sql.append(" and po.person_name like ?");
            paramList.add('%'+queryParam.get("personName").toString()+'%');
           // paramList.add(queryParam.get("personName"));
        }
        if(!StringUtils.isEmpty(queryParam.get("idenNo"))) {
            sql.append(" and po.iden_no like ?");
            paramList.add('%'+queryParam.get("idenNo").toString()+'%');
           // paramList.add(queryParam.get("idenNo"));
        }
        if(!StringUtils.isEmpty(queryParam.get("deptid"))) {
            sql.append(" and po.dept_id = ?");
            paramList.add(queryParam.get("deptid"));
        }
        if(!StringUtils.isEmpty(queryParam.get("state"))){
            sql.append(" and po.state=?");
            paramList.add(queryParam.get("state"));
        }
        if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
            sql.append(" and po.mof_div_code=?");
            paramList.add(queryParam.get("mofDivCode"));
        }
        if(!StringUtils.isEmpty(queryParam.get("personnelCode"))){
            sql.append(" and po.personnel_code=?");
            paramList.add(queryParam.get("personnelCode"));
        }
        sql.append(" ) a");
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramList.toArray());
    }

    @Override
    public int getPersonWaitInPersonTotal(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append(" count(*) ");
        sql.append("FROM ");
        sql.append(" sal_person_operate spo ");
        sql.append("JOIN ( ");
        sql.append(" SELECT ");
        sql.append("		max(update_time) maxTime, ");
        sql.append("		person_id ");
        sql.append(" FROM ");
        sql.append("		sal_person_operate ");
        sql.append(" WHERE ");
        sql.append("		agency_id = ? ");
        sql.append(" AND operate_code = 'in' ");
        sql.append(" GROUP BY ");
        sql.append("		person_id ");
        sql.append(") t ON t.person_id = spo.person_id ");
        sql.append("AND spo.update_time = t.maxTime ");
        sql.append("where ( verify_state is null or verify_state in ('0', '-1')) ");
        Object[] objects = new Object[] {
                queryParam.get("agencyId")
        };
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, objects);
    }

    @Override
    public Map<String, Object> getStateNum(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select sum(decode(state , '3', 1, 0)) as \"registerNum\", sum(decode(state , '1', 1, 0)) as \"quitNum\" ");
        sql.append(" from ").append(TableName.PERSON_OPERATE);
        sql.append("  where update_time in ( select max(update_time) from  ").append(TableName.PERSON_OPERATE).append(" where is_deleted= ?");
        paramList.add(SystemDictConstant.IS_FALSE);
        sql.append(" and operate_code != ? ");
        paramList.add(SystemDictConstant.CHECK_IDEN_NO);
                /*.append(" or (operate_code = '")
                .append(SystemDictConstant.CHECK_IDEN_NO)
                .append("' and verify_state = '")
                .append(SystemDictConstant.IS_AUDIT).append("')");*/
        sql.append(" group by person_id )");
        sql.append(" AND if (((operate_code = ? or operate_code = ?) and verify_state = ? ), 1, is_deleted) = ?");
        paramList.add(SystemDictConstant.DELETE_PERSON);
        paramList.add(SystemDictConstant.OUT_PERSON);
        paramList.add(SystemDictConstant.IS_AUDIT);
        paramList.add(SystemDictConstant.IS_FALSE);
        sql.append(" and agency_id = ?");
        paramList.add(queryParam.get("agencyId"));
        return this.getJdbcTemplate().queryForMap(sql.toString(), paramList.toArray());
    }

    @Override
    public List<PersonOperate> getPersonOperateRecord(String agencyId, String personId, String operateCode) {
        List<Object> params = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" person_id, person_name,create_time, sex_code, iden_type_code, operate_code, iden_no, nation, nat_code, ")
                .append(" birthday, state, work_init_date, iden_is_verify, fundmode, out_to_agency, in_from_agency, delete_type, ")
                .append(" person_address, agency_id, dept_id, agency_code, enter_agency_date, retire_date, ")
                .append(" person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no, ")
                .append(" sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code, ")
                .append(" sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state, ")
                .append(" change_state, verify_state, iden_verify_state, per_source_code, is_special_post, ")
                .append(" is_insurance, is_uni_sala,phone ");
        sql.append(" from ").append(TableName.PERSON_OPERATE);
        // agency_id = ? and
        sql.append(" where agency_id = ? and person_id = ? and verify_state = ?");
        params.add(agencyId);
        params.add(personId);
        params.add(SystemDictConstant.IS_AUDIT);
        if (!StringUtils.isEmpty(operateCode)) {
            // 编辑查所有，不是编辑按照operate_code查询
            if (!SystemDictConstant.EDIT_PERSON.equals(operateCode)) {
                // 如果是调入，则查询调出和调入的记录，以判断是由那个单位调出到本单位的
                if (SystemDictConstant.IN_PERSON.equals(operateCode)) {
                    List<String> operateList = new ArrayList<>(2);
                    operateList.add(SystemDictConstant.OUT_PERSON);
                    operateList.add(SystemDictConstant.IN_PERSON);
                    sql.append(" and ");
                    sql.append(SysUtils.inSql("operate_code", operateList));
                } else {
                    sql.append(" and operate_code = ?");
                    params.add(operateCode);
                }
            }
        }
        sql.append(" order by create_time, operate_code");
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(PersonOperate.class),params.toArray());
    }

    @Override
    public PersonOperate getOldIdenNo(String personid) {
        List<Object> params = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select id,create_time,update_time,person_id,person_name,sex_code,iden_type_code,operate_code,iden_no,nation,nat_code, ")
                .append("birthday,state,work_init_date,iden_is_verify,fundmode,out_to_agency,delete_type,person_address,agency_id,")
                .append("dept_id,agency_code,enter_agency_date,retire_date,person_type_code,person_ide_code,end_date,dead_date,remarks,")
                .append("sala_card_no,sala_card_bank,gov_cred_card_no,gov_cred_card_bank,start_date,merit_pay_code,sch_rec_code,pos_code,gr_code,")
                .append("tec_gr_code,is_auth,sala_gr_code,person_state,change_state,verify_state,per_source_code,is_special_post,is_insurance,")
                .append("is_uni_sala,is_deleted,iden_verify_state,mof_div_code, person_type_id, post_id, grade_id, level_id, show_order, personnel_code,phone, ")
                .append("NATURE_OF_FUNDS_CODE,FUNCTIONAL_SUBJECTS_CODE,DEPT_ECONOMIC_SUBJECTS_CODE,GOV_DEPT_CLASS_CODE")
                .append(" from ").append(TableName.PERSON_OPERATE)
                .append("  where is_deleted= ? and (operate_code in(?,?,?) and verify_state=?)");
        params.add(SystemDictConstant.IS_FALSE);
        params.add(SystemDictConstant.ADD_PERSON);
        params.add(SystemDictConstant.IN_PERSON);
        params.add(SystemDictConstant.EDIT_PERSON);
        params.add(SystemDictConstant.IS_AUDIT);
        if(!StringUtils.isEmpty(personid)){
            sql.append(" and person_id= ? ");
            params.add(personid);
        }
        sql.append(" and update_time=( select max(update_time) from ").append(TableName.PERSON_OPERATE)
                .append(" where is_deleted = ? and (operate_code IN (?,?,?) and verify_state = ?)");
        params.add(SystemDictConstant.IS_FALSE);
        params.add(SystemDictConstant.ADD_PERSON);
        params.add(SystemDictConstant.IN_PERSON);
        params.add(SystemDictConstant.EDIT_PERSON);
        params.add(SystemDictConstant.IS_AUDIT);
        if(!StringUtils.isEmpty(personid)){
            sql.append(" and person_id = ? )");
            params.add(personid);
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(PersonOperate.class), params.toArray());
    }

    /**
     * 获取所有未生效的数据
     * @return
     */
    @Override
    public List<PersonOperate> getPersonNotReal() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from ").append(TableName.PERSON_OPERATE);
        sql.append(" where verify_state = ? and is_real = ?");
        Object[] params = new Object[] {
          SystemDictConstant.IS_AUDIT, SystemDictConstant.IS_FALSE
        };
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(PersonOperate.class), params);
    }

    @Override
    public void batchUpdateBank(List<Object[]> params) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.PERSON_OPERATE);
        sql.append(" set sala_card_bank = ? , sala_card_no = ? ");
        sql.append(" where iden_no = ? ");
        this.getJdbcTemplate().batchUpdate(sql.toString(),  new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 3; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public UnitSalaryReportVO selectPersonMinusAndPlus(String agencyId, String salaryMonth) {
        StringBuilder sql = new StringBuilder();
        sql.append("select nvl(sum(case when operate_code in ('add','in') and state='3' then 1 else 0 end),0) workPlusTotal ,")
           .append(" nvl(sum(case when operate_code in ('add','in') and state='1' then 1 else 0 end),0) retirePlusTotal,")
           .append(" nvl(sum(case when operate_code in ('delete','out') and state='3' then 1 else 0 end),0) workMinusTotal,")
           .append(" nvl(sum(case when operate_code in ('delete','out') and state='1' then 1 else 0 end),0) retireMinusTotal")
           .append(" from ").append(TableName.PERSON_OPERATE)
           .append(" where  is_deleted = 0 and verify_state = '2' and is_real = 1 and agency_id = ? and salary_month = ?");
        return super.getjdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(UnitSalaryReportVO.class), agencyId, salaryMonth);
    }

    @Override
    public void updateVerifyStateById(String id, String verifyState) {
        StringBuilder sb = new StringBuilder();
        sb.append(" update ").append(TableName.PERSON_OPERATE)
                .append(" set ").append(" verify_state = ? ")
                .append(" where ").append(" id = ? and is_deleted = 0 ");
        super.getjdbcTemplate().update(sb.toString(), verifyState, id);
    }

    @Override
    public void physicalDeleteById(String operateId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ").append(TableName.PERSON_OPERATE)
                .append(" where ").append(" id = ? ");
        super.getjdbcTemplate().update(sb.toString(), operateId);
    }

    @Override
    public void physicalDeleteInPerson(String personId, String inFromAgency, String salaryMonth) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ").append(TableName.PERSON_OPERATE)
                .append(" where ").append(" person_id = ? and in_from_agency = ? and salary_month = ? ");
        super.getjdbcTemplate().update(sb.toString(), personId, inFromAgency, salaryMonth);
    }

    @Override
    public int selectInPersonNumByPersonId(String personId, String inFromAgency, String salaryMonth) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select count(*) from ").append(TableName.PERSON_OPERATE)
                .append(" where ").append(" operate_code = ? and person_id = ? ")
                .append(" and in_from_agency = ? and salary_month = ? ");
        Object[] params = {
                SystemDictConstant.IN_PERSON, personId, inFromAgency, salaryMonth
        };
        return super.getjdbcTemplate().queryForObject(sb.toString(), params, int.class);
    }

    @Override
    public PersonOperate selectHistoryAddOperateByPersonId(String personId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" person_address, dept_id, enter_agency_date, retire_date, ")
                .append(" person_type_code, person_ide_code, end_date, dead_date, ")
                .append(" remarks, sala_card_no, sala_card_bank, gov_cred_card_no, ")
                .append(" gov_cred_card_bank, start_date, merit_pay_code, sch_rec_code, ")
                .append(" pos_code, gr_code, tec_gr_code, is_auth, ")
                .append(" sala_gr_code, person_state, change_state, per_source_code, ")
                .append(" is_special_post, is_insurance, is_uni_sala,phone,func_id,func_name,func_code ")
                .append(" from ").append(TableName.PERSON_OPERATE)
                .append(" where ").append(" is_deleted = 0 ")
                .append(" and operate_code = ? and verify_state = ? and person_id = ? ");
        Object[] params = {
                SystemDictConstant.ADD_PERSON, SystemDictConstant.IS_AUDIT, personId
        };
        return super.getjdbcTemplate().queryForObject(sb.toString(), params, new BeanPropertyRowMapper<>(PersonOperate.class));
    }

    @Override
    public List<PersonOperate> getPersonOperatePersonState(String agencyId, String personId, String personState) {
        List<Object> params = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" person_id, person_name,create_time, sex_code, iden_type_code, operate_code, iden_no, nation, nat_code, ")
                .append(" birthday, state, work_init_date, iden_is_verify, fundmode, out_to_agency, in_from_agency, delete_type, ")
                .append(" person_address, agency_id, dept_id, agency_code, enter_agency_date, retire_date, ")
                .append(" person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no, ")
                .append(" sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code, ")
                .append(" sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state, ")
                .append(" change_state, verify_state, iden_verify_state, per_source_code, is_special_post, ")
                .append(" is_insurance, is_uni_sala,phone,func_id,func_name,func_code ");
        sql.append(" from ").append(TableName.PERSON_OPERATE);
        // agency_id = ? and
        sql.append(" where agency_id = ? and person_id = ? ");
        params.add(agencyId);
        params.add(personId);
        if(!StringUtils.isEmpty(personState)){
            sql.append(" and operate_code=? and verify_state is null");
            params.add(SystemDictConstant.IN_PERSON);
        }

        sql.append(" order by create_time, operate_code");
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(PersonOperate.class),params.toArray());
    }

    @Override
    public List<PersonOperate> getPersonOperateCodeByIdenNos(List<String> IdenNo,String salary_month) {
        StringBuilder sql = new StringBuilder();
        List<String> params=new ArrayList<>();
        sql.append(" select ")
                .append("pi.id, pi.create_time, pi.update_time, pe.person_id, pi.person_name, pi.sex_code, iden_type_code,")
                .append("pi.iden_no, pi.nation, pe.verify_state, ")
                .append("nat_code, birthday, pi.state, work_init_date, iden_is_verify, fundmode,  ")
                .append("person_address, pe.agency_id, dept_id, agency_code, enter_agency_date,")
                .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state, ")
                .append("change_state, per_source_code, is_special_post, is_insurance, is_uni_sala, pi.is_deleted ,iden_verify_state, pi.mof_div_code, ")
                .append(" person_type_id, post_id, grade_id, level_id,? salary_month,fi.middle_id existsFile,phone ");
        params.add(salary_month);
        sql.append(" from ").append(TableName.PERSON_INFO).append(" pi ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pe")
                .append(" on pi.id = pe.person_id ")
                .append(" left join (select distinct agency_id,middle_id from ").append(TableName.SAL_FILE_INFO)
                .append("  where  salary_month=?  ) fi")
                .append(" on pi.id=fi.middle_id  and pe.agency_id=fi.agency_id  ");
        params.add(salary_month);
        sql.append(" where ").append(SysUtils.inSql("pe.iden_no", IdenNo));
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(PersonOperate.class),params.toArray());
    }


    @Override
    public void batchInsertUpdate(List<PersonOperate> personOperates) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.PERSON_OPERATE);
        sql.append("(id, create_time, update_time, person_id, person_name, sex_code, iden_type_code,")
                .append("operate_code, iden_no, nation, verify_state, delete_type,")
                .append("nat_code, birthday, state, work_init_date, iden_is_verify, fundmode, out_to_agency, in_from_agency, ")
                .append("person_address, agency_id, dept_id, agency_code, enter_agency_date,")
                .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state, ")
                .append("change_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_deleted ,iden_verify_state,is_real,salary_month, mof_div_code, ")
                .append(" person_type_id, post_id, grade_id, level_id,phone,func_id,func_name,func_code)");
        sql.append("values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,")
                .append("?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        List<Object[]> paramsList=new ArrayList<>();
        for(PersonOperate personOperate:personOperates) {
            Object[] params = new Object[]{
                    SysUtils.generateGuid(), String.valueOf(DateUtils.getCurrentTimeStamp()), String.valueOf(DateUtils.getCurrentTimeStamp()),
                    personOperate.getPersonId(), personOperate.getPersonName(), personOperate.getSexCode(),
                    personOperate.getIdenTypeCode(), "edit", personOperate.getIdenNo(),
                    personOperate.getNation(), 2, personOperate.getDeleteType(),
                    personOperate.getNatCode(), personOperate.getBirthday(), personOperate.getState(),
                    personOperate.getWorkInitDate(), personOperate.getIdenIsVerify(), personOperate.getFundmode(),
                    personOperate.getOutToAgency(), personOperate.getInFromAgency(), personOperate.getPersonAddress(), personOperate.getAgencyId(),
                    personOperate.getDeptId(), personOperate.getAgencyCode(), personOperate.getEnterAgencyDate(),
                    personOperate.getRetireDate(), personOperate.getPersonTypeCode(), personOperate.getPersonIdeCode(),
                    personOperate.getEndDate(), personOperate.getDeadDate(), personOperate.getRemarks(),
                    personOperate.getSalaCardNo(), personOperate.getSalaCardBank(), personOperate.getGovCredCardNo(),
                    personOperate.getGovCredCardBank(), personOperate.getStartDate(), personOperate.getMeritPayCode(),
                    personOperate.getSchRecCode(), personOperate.getPosCode(), personOperate.getGrCode(),
                    personOperate.getTecGrCode(), personOperate.getIsAuth(), personOperate.getSalaGrCode(),
                    personOperate.getPersonState(), personOperate.getChangeState(), personOperate.getPerSourceCode(),
                    personOperate.getIsSpecialPost(), personOperate.getIsInsurance(), personOperate.getIsUniSala(),
                    0, personOperate.getIdenVerifyState(), 1,
                    personOperate.getSalaryMonth(), personOperate.getMofDivCode(), personOperate.getPersonTypeId(),
                    personOperate.getPostId(), personOperate.getGradeId(), personOperate.getLevelId(),personOperate.getPhone(),
                    personOperate.getFuncId(),personOperate.getFuncName(),personOperate.getFuncCode()
            };
            paramsList.add(params);
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(),  new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 62; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }


    @Override
    public List<String> selectChangePersonByAgencyMonth(String agencyId, String salaryMonth) {
        List<Object> params = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select distinct person_id ")
                .append(" from ").append(TableName.PERSON_OPERATE)
                .append("  where is_deleted= 0 and salary_month=? and agency_id=? and verify_state=2 ");
        params.add(salaryMonth);
        params.add(agencyId);

        return super.getjdbcTemplate().queryForList(sql.toString(), params.toArray(), String.class);
    }

    @Override
    public Integer queryExistingNumber(String agencyId, String payCode) {
        StringBuilder sql = new StringBuilder();
        sql.append("select COUNT(*) from ")
                .append( TableName.PERSON_EXT);
        sql.append(" where agency_id=? and merit_pay_code=? and is_deleted='0' ");
        Object[] params = new Object[]{
                agencyId,payCode
        };
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, params);
    }

    @Override
    public void batchUpdateAutoMonthEnd(List<Object[]> params) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.PERSON_OPERATE).append(" set ")
                .append(" salary_month = ? ");
        sql.append(" where agency_id = ? and salary_month = ?");
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 3; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void batchUpdateAutoFile(List<Object[]> params){
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SAL_FILE_INFO).append(" set ")
                .append(" salary_month = ? ");
        sql.append(" where agency_id = ? and salary_month = ?");
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 3; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void updatePersonOperateCode(List<AgencyVerify> updateList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.PERSON_OPERATE);
        sql.append(" set  agency_code=?   where agency_id=?");
        List<Object[]> paramsList = new ArrayList<>(updateList.size());
        for (AgencyVerify personOperate : updateList) {
            Object[] params = new Object[]{
                    personOperate.getAgencyCode(),personOperate.getAgencyId()
            };
            paramsList.add(params);
        }
        super.getJdbcTemplate().batchUpdate(sql.toString(),  new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }
}
